{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "321adc7f-f386-4dbf-bb9c-d5f789421f78",
   "metadata": {},
   "source": [
    "# Setup and Explore Databases\n",
    "\n",
    "## Overview\n",
    "\n",
    "This notebook is the first step in the lab, designed to finalize the setup of the database environment and to familiarize you with the database schemas and data. The core infrastructure, including an AlloyDB cluster and a Spanner instance, is already provisioned via Terraform. The notebook will guide you through the final setup steps for these databases and allow you to explore their structure and content. \n",
    "\n",
    "The key milestones you will achieve are:\n",
    "\n",
    "- Connecting to your Google Cloud project and the provisioned databases.\n",
    "- Finalizing the setup of the AlloyDB and Spanner databases.\n",
    "- Importing and validating data in both AlloyDB and Spanner.\n",
    "- Updating the dates in the datasets to demonstrate real-time capabilities.\n",
    "- Exploring the schemas and data in both databases through various queries.\n",
    "\n",
    "### Terraform Resources\n",
    "\n",
    "The following pre-requisite resources were created for you by Terraform. See the [main.tf](../terraform/main.tf) file for more details on the environment configuration:\n",
    "\n",
    "- Spanner, AlloyDB, and other related APIs\n",
    "- Network components for secure communication (Custom VPC, Cloud Router, Cloud NAT Gateway, Firewall Rules, Private IP Range for Service Networking)\n",
    "- AlloyDB Cluster and Instance\n",
    "- Spanner Instance, Database, Schema, and Data\n",
    "- Vertex AI Workbench Instance\n",
    "\n",
    "### Google Cloud Services Used in this Notebook\n",
    "\n",
    "This notebook utilizes the following Google Cloud services:\n",
    "- Vertex AI Workbench: A Jupyter notebook-based development environment for the entire data science workflow.\n",
    "- AlloyDB: A fully-managed, 100% PostgreSQL-compatible database service to power your most demanding enterprise workloads with superior performance, availability, and scale, and supercharge them with AI.\n",
    "- Spanner: A fully-managed, geographically distributed relational database with unlimited scale, strong consistency, and up to 99.999% availability, bringing together relational, graph, key value, vector, and search data into a single database.\n",
    "- Cloud Storage: A managed service for storing any amount of data unstructured data retrieving it as often as you like.\n",
    "- IAM (Identity and Access Management): Fine-grained access control and visibility for centrally managing cloud resources.\n",
    "\n",
    "### Logical Flow\n",
    "This notebook provides step-by-step instructions to guide you through the database setup and exploration process. The logical flow is as follows:\n",
    "- Basic Setup: This section includes defining notebook variables, connecting to your Google Cloud project, configuring logging, and installing dependencies.\n",
    "- Helper Functions: Helper functions are defined for interacting with the REST API, AlloyDB, and Spanner to simplify the code in the subsequent sections.\n",
    "- Finalize AlloyDB Setup: This section connects to the AlloyDB cluster, creates the finance database, and imports data from a SQL file in a GCS bucket. It also includes a step to validate the row counts of the imported tables.\n",
    "- Update Dates: The dates in both the AlloyDB and Spanner tables are updated to reflect more recent data, which is crucial for demonstrating the real-time capabilities of the solution.\n",
    "- Explore AlloyDB Data: This section provides a review of the AlloyDB schema and allows you to run queries to explore the data in the various tables.\n",
    "- Explore Spanner Data: Similar to the AlloyDB section, this part of the notebook reviews the Spanner schema (including the property graph) and allows you to run both relational and graph queries to explore the data.\n",
    "\n",
    "Let's get started!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0927ae02-125e-4d3e-a885-1e437e6bddb6",
   "metadata": {},
   "source": [
    "## Basic Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45c7ec06",
   "metadata": {},
   "source": [
    "### Review Organization Policies\n",
    "\n",
    "If you have [Organization Policies](https://console.cloud.google.com/iam-admin/orgpolicies/list) that explicitly deny certain configurations, you will need to work with your platform team to add an exception. Specifically, you may need to override the following policies with an \"Allow All\" rule (see [the docs](https://cloud.google.com/resource-manager/docs/organization-policy/creating-managing-policies) for more details): `constraints/run.allowedIngress`, `constraints/iam.allowedPolicyMemberDomains`, `constraints/compute.vmExternalIpAccess`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "842a3ca3-4f62-4190-bbb6-10e7da9b13c8",
   "metadata": {},
   "source": [
    "### Define Notebook Variables\n",
    "\n",
    "Update the `project_id` and `region` variables below to match your environment. You can find these values in your Terraform output. You can use defaults for the rest of the project variables .\n",
    "\n",
    "You will be prompted for two passwords:\n",
    "1. The AlloyDB admin password for the `postgres` user. This is the password you defined when provisioning the Terraform environment. \n",
    "2. A new password for a least-privilege `toolbox_user` account that we will create later in the notebook. Take note of this password for later labs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4fa431b-2efd-4db1-89e1-b97b4fc722df",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Project variables\n",
    "project_id = \"my-project\"\n",
    "region = \"my-region\"\n",
    "vpc = \"demo-vpc\"\n",
    "gcs_bucket_name = f\"project-files-{project_id}\"\n",
    "\n",
    "# AlloyDB variables\n",
    "alloydb_cluster = \"my-alloydb-cluster\"\n",
    "alloydb_instance = \"my-alloydb-instance\"\n",
    "alloydb_database = \"finance\"\n",
    "alloydb_password = input(\"Please enter the password for the AlloyDB 'postgres' database user: \")\n",
    "alloydb_toolbox_user_password = input(\"Please choose a password for the new AlloyDB 'toolbox_user' database user: \")\n",
    "\n",
    "# Spanner variables\n",
    "spanner_instance = \"my-spanner-instance\"\n",
    "spanner_database = \"finance-graph\"\n",
    "session = None\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "01db8a8b-cae4-4978-bb9a-e2e5a8465696",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Set env variable to suppress annoying system warnings when running shell commands\n",
    "%env GRPC_ENABLE_FORK_SUPPORT=1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62bc39dc-f6ee-455e-853a-643c12918c2f",
   "metadata": {},
   "source": [
    "### Connect to your Google Cloud Project"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d972baa-ffa7-47c7-a965-fdaa114262b5",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Configure gcloud.\n",
    "!gcloud config set project {project_id}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38a124be-854e-4465-bab2-44ea855d9256",
   "metadata": {},
   "source": [
    "### Configure Logging"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66056e25-5e3a-47e3-a842-8fe6fadd9a33",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import logging\n",
    "import sys\n",
    "\n",
    "# Configure the root logger to output messages with INFO level or above\n",
    "logging.basicConfig(level=logging.INFO, stream=sys.stdout, format='%(asctime)s[%(levelname)5s][%(name)14s] - %(message)s',  datefmt='%H:%M:%S', force=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b72eb606-f634-424f-be79-c1b75b1b1e14",
   "metadata": {},
   "source": [
    "### Install Dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7c2e387-9dfc-49c2-8a32-2c54d7eebcc2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "! pip install --quiet google-cloud-storage==2.19.0 \\\n",
    "                      asyncpg==0.30.0 \\\n",
    "                      google.cloud.alloydb.connector==1.9.0 "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db5cf49a-a04d-429c-a5f3-2df8e068fc81",
   "metadata": {},
   "source": [
    "### Define Helper Functions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "349aa29e-ccbb-4aef-b51b-20d9130c1e53",
   "metadata": {},
   "source": [
    "#### REST API Helper Function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0a0ad2b-fed0-434f-baa7-69247ee476c7",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import google.auth\n",
    "import json\n",
    "\n",
    "# Get an access token based upon the current user\n",
    "creds, _ = google.auth.default()\n",
    "authed_session = google.auth.transport.requests.AuthorizedSession(creds)\n",
    "access_token=creds.token\n",
    "\n",
    "if project_id:\n",
    "  authed_session.headers.update({\"x-goog-user-project\": project_id}) # Required to workaround a project quota bug\n",
    "\n",
    "def rest_api_helper(\n",
    "    url: str,\n",
    "    http_verb: str,\n",
    "    request_body: dict = None,\n",
    "    params: dict = None,\n",
    "    session: requests.Session = authed_session,\n",
    "  ) -> dict:\n",
    "  \"\"\"Calls a REST API using a pre-authenticated requests Session.\"\"\"\n",
    "\n",
    "  headers = {\"Content-Type\": \"application/json\"}\n",
    "\n",
    "  try:\n",
    "\n",
    "    if http_verb == \"GET\":\n",
    "      response = session.get(url, headers=headers, params=params)\n",
    "    elif http_verb == \"POST\":\n",
    "      response = session.post(url, json=request_body, headers=headers, params=params)\n",
    "    elif http_verb == \"PUT\":\n",
    "      response = session.put(url, json=request_body, headers=headers, params=params)\n",
    "    elif http_verb == \"PATCH\":\n",
    "      response = session.patch(url, json=request_body, headers=headers, params=params)\n",
    "    elif http_verb == \"DELETE\":\n",
    "      response = session.delete(url, headers=headers, params=params)\n",
    "    else:\n",
    "      raise ValueError(f\"Unknown HTTP verb: {http_verb}\")\n",
    "\n",
    "    # Raise an exception for bad status codes (4xx or 5xx)\n",
    "    response.raise_for_status()\n",
    "\n",
    "    # Check if response has content before trying to parse JSON\n",
    "    if response.content:\n",
    "        return response.json()\n",
    "    else:\n",
    "        return {} # Return empty dict for empty responses (like 204 No Content)\n",
    "\n",
    "  except requests.exceptions.RequestException as e:\n",
    "      # Catch potential requests library errors (network, timeout, etc.)\n",
    "      # Log detailed error information\n",
    "      print(f\"Request failed: {e}\")\n",
    "      if e.response is not None:\n",
    "          print(f\"Request URL: {e.request.url}\")\n",
    "          print(f\"Request Headers: {e.request.headers}\")\n",
    "          print(f\"Request Body: {e.request.body}\")\n",
    "          print(f\"Response Status: {e.response.status_code}\")\n",
    "          print(f\"Response Text: {e.response.text}\")\n",
    "          # Re-raise a more specific error or a custom one\n",
    "          raise RuntimeError(f\"API call failed with status {e.response.status_code}: {e.response.text}\") from e\n",
    "      else:\n",
    "          raise RuntimeError(f\"API call failed: {e}\") from e\n",
    "  except json.JSONDecodeError as e:\n",
    "      print(f\"Failed to decode JSON response: {e}\")\n",
    "      print(f\"Response Text: {response.text}\")\n",
    "      raise RuntimeError(f\"Invalid JSON received from API: {response.text}\") from e\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c0f4658-e4c5-4c3f-badf-da3f3d6cdd3a",
   "metadata": {},
   "source": [
    "#### AlloyDB Helper Function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0b976a5-7b18-41d2-89c9-dbd2b9a2aa45",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Create AlloyDB Query Helper Function\n",
    "import sqlalchemy\n",
    "from sqlalchemy import text, exc\n",
    "import pandas as pd\n",
    "\n",
    "async def run_alloydb_query(pool, sql: str, params = None, output_as_df: bool = True):\n",
    "    \"\"\"Executes a SQL query or statement against the database pool.\n",
    "\n",
    "    Handles various SQL statements:\n",
    "    - SELECT/WITH: Returns results as a DataFrame (if output_as_df=True)\n",
    "      or ResultProxy. Supports parameters. Does not commit.\n",
    "    - EXPLAIN/EXPLAIN ANALYZE: Executes the explain, returns the query plan\n",
    "      as a formatted multi-line string. Ignores output_as_df.\n",
    "      Supports parameters. Does not commit.\n",
    "    - INSERT/UPDATE/DELETE/CREATE/ALTER etc.: Executes the statement,\n",
    "      commits the transaction, logs info, and returns the ResultProxy.\n",
    "      Supports single or bulk parameters (executemany).\n",
    "\n",
    "    Args:\n",
    "      pool: An asynchronous SQLAlchemy connection pool.\n",
    "      sql: A string containing the SQL query or statement template.\n",
    "      params: Optional.\n",
    "        - None: Execute raw SQL (Use with caution for non-SELECT/EXPLAIN).\n",
    "        - dict or tuple: Parameters for a single execution.\n",
    "        - list of dicts/tuples: Parameters for bulk execution (executemany).\n",
    "      output_as_df (bool): If True and query is SELECT/WITH, return pandas DataFrame.\n",
    "                           Ignored for EXPLAIN and non-data-returning statements.\n",
    "\n",
    "    Returns:\n",
    "      pandas.DataFrame | str | sqlalchemy.engine.Result | None:\n",
    "        - DataFrame: For SELECT/WITH if output_as_df=True.\n",
    "        - str: For EXPLAIN/EXPLAIN ANALYZE, containing the formatted query plan.\n",
    "        - ResultProxy: For non-SELECT/WITH/EXPLAIN statements, or SELECT/WITH\n",
    "                       if output_as_df=False.\n",
    "        - None: If a SQLAlchemy ProgrammingError or other specific error occurs.\n",
    "\n",
    "    Raises:\n",
    "        Exception: Catches and logs `sqlalchemy.exc.ProgrammingError`, returning None.\n",
    "                   May re-raise other database exceptions.\n",
    "\n",
    "    Example Execution:\n",
    "      Single SELECT:\n",
    "        sql_select = \"SELECT ticker, company_name from investments LIMIT 5\"\n",
    "        df_result = await run_alloydb_query(pool, sql_select)\n",
    "\n",
    "      Single non-SELECT - Parameterized (Safe!):\n",
    "        Parameterized INSERT:\n",
    "          sql_insert = \"INSERT INTO investments (ticker, company_name) VALUES (:ticker, :name)\"\n",
    "          params_insert = {\"ticker\": \"NEW\", \"name\": \"New Company\"}\n",
    "          insert_result = await run_alloydb_query(pool, sql_insert, params_insert)\n",
    "\n",
    "        Parameterized UPDATE:\n",
    "          sql_update = \"UPDATE products SET price = :price WHERE id = :product_id\"\n",
    "          params_update = {\"price\": 99.99, \"product_id\": 123}\n",
    "          update_result = await run_alloydb_query(pool, sql_update, params_update)\n",
    "\n",
    "      Bulk Update:\n",
    "        docs = pd.DataFrame([\n",
    "            {'id': 101, 'sparse_embedding': '[0.1, 0.2]'},\n",
    "            {'id': 102, 'sparse_embedding': '[0.3, 0.4]'},\n",
    "            # ... more rows\n",
    "        ])\n",
    "\n",
    "        update_sql_template = '''\n",
    "            UPDATE products\n",
    "            SET sparse_embedding = :embedding,\n",
    "                sparse_embedding_model = 'BM25'\n",
    "            WHERE id = :product_id\n",
    "        ''' # Using named parameters :param_name\n",
    "\n",
    "        # Prepare list of dictionaries for params\n",
    "        data_to_update = [\n",
    "            {\"embedding\": row.sparse_embedding, \"product_id\": row.id}\n",
    "            for row in docs.itertuples(index=False)\n",
    "        ]\n",
    "\n",
    "        if data_to_update:\n",
    "          bulk_result = await run_alloydb_query(pool, update_sql_template, data_to_update)\n",
    "          # bulk_result is the SQLAlchemy ResultProxy\n",
    "\n",
    "    \"\"\"\n",
    "    sql_lower_stripped = sql.strip().lower()\n",
    "    is_select_with = sql_lower_stripped.startswith(('select', 'with'))\n",
    "    is_explain = sql_lower_stripped.startswith('explain')\n",
    "\n",
    "    # Determine if the statement is expected to return data rows or a plan\n",
    "    is_data_returning = is_select_with or is_explain\n",
    "\n",
    "    # Determine actual DataFrame output eligibility (only for SELECT/WITH)\n",
    "    effective_output_as_df = output_as_df and is_select_with\n",
    "\n",
    "    # Check if params suggest a bulk operation (for logging purposes)\n",
    "    is_bulk_operation = isinstance(params, (list, tuple)) and len(params) > 0 and isinstance(params[0], (dict, tuple, list))\n",
    "\n",
    "    async with pool.connect() as conn:\n",
    "        try:\n",
    "          # Execute with or without params\n",
    "          if params:\n",
    "              result = await conn.execute(text(sql), params)\n",
    "          else:\n",
    "              # Add warning for raw SQL only if it's NOT data-returning\n",
    "              #if not is_data_returning:\n",
    "                  #logging.warning(\"Executing non-SELECT/EXPLAIN raw SQL without parameters. Ensure SQL is safe.\")\n",
    "              result = await conn.execute(text(sql))\n",
    "\n",
    "          # --- Handle statements that return data or plan ---\n",
    "          if is_data_returning:\n",
    "              if is_explain:\n",
    "                  # Fetch and format EXPLAIN output as a string\n",
    "                    try:\n",
    "                        plan_rows = result.fetchall()\n",
    "                        # EXPLAIN output is usually text in the first column\n",
    "                        query_plan = \"\\n\".join([str(row[0]) for row in plan_rows])\n",
    "                        #logging.info(f\"EXPLAIN executed successfully for: {sql[:100]}...\")\n",
    "                        return query_plan\n",
    "                    except Exception as e:\n",
    "                        logging.error(f\"Error fetching/formatting EXPLAIN result: {e}\")\n",
    "                        return None\n",
    "              else: # Handle SELECT / WITH\n",
    "                  if effective_output_as_df:\n",
    "                      try:\n",
    "                          rows = result.fetchall()\n",
    "                          column_names = result.keys()\n",
    "                          df = pd.DataFrame(rows, columns=column_names)\n",
    "                          #logging.info(f\"SELECT/WITH executed successfully, returning DataFrame for: {sql[:100]}...\")\n",
    "                          return df\n",
    "                      except Exception as e:\n",
    "                          logging.error(f\"Error converting SELECT result to DataFrame: {e}\")\n",
    "                          logging.info(f\"Returning raw ResultProxy for SELECT/WITH due to DataFrame conversion error for: {sql[:100]}...\")\n",
    "                          return result # Fallback to raw result\n",
    "                  else:\n",
    "                      # Return raw result proxy for SELECT/WITH if df output not requested\n",
    "                      #logging.info(f\"SELECT/WITH executed successfully, returning ResultProxy for: {sql[:100]}...\")\n",
    "                      return result\n",
    "\n",
    "          # --- Handle Non-Data Returning Statements (INSERT, UPDATE, DELETE, CREATE, etc.) ---\n",
    "          else:\n",
    "              await conn.commit() # Commit changes ONLY for these statements\n",
    "              operation_type = sql.strip().split()[0].upper()\n",
    "              row_count = result.rowcount # Note: rowcount behavior varies\n",
    "\n",
    "              if is_bulk_operation:\n",
    "                  print(f\"Bulk {operation_type} executed for {len(params)} items. Result rowcount: {row_count}\")\n",
    "              elif operation_type in ['INSERT', 'UPDATE', 'DELETE']:\n",
    "                  print(f\"{operation_type} statement executed successfully. {row_count} row(s) affected.\")\n",
    "              else: # CREATE, ALTER, etc.\n",
    "                  print(f\"{operation_type} statement executed successfully. Result rowcount: {row_count}\")\n",
    "              return result # Return the result proxy\n",
    "\n",
    "        except exc.ProgrammingError as e:\n",
    "            # Log the error with context\n",
    "            logging.error(f\"SQL Programming Error executing query:\\nSQL: {sql[:500]}...\\nParams (sample): {str(params)[:500]}...\\nError: {e}\")\n",
    "            # Rollback might happen automatically on context exit with error, but explicit can be clearer\n",
    "            # await conn.rollback() # Consider if needed based on pool/transaction settings\n",
    "            return None # Return None on handled programming errors\n",
    "        except Exception as e:\n",
    "            # Log other unexpected errors\n",
    "            logging.error(f\"An unexpected error occurred during query execution:\\nSQL: {sql[:500]}...\\nError: {e}\")\n",
    "            # await conn.rollback() # Consider if needed\n",
    "            raise # Re-raise unexpected errors\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0da64a98-6bd8-44fe-88d1-c6487659bebb",
   "metadata": {},
   "source": [
    "#### Spanner Helper Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34357fd5-5db1-4ddd-b0c0-805b9c6eae4a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import time\n",
    "\n",
    "\n",
    "def get_spanner_sessions(project_id = project_id, spanner_instance = spanner_instance, spanner_database = spanner_database):\n",
    "\n",
    "  # Get session\n",
    "  uri = f\"https://spanner.googleapis.com/v1/projects/{project_id}/instances/{spanner_instance}/databases/{spanner_database}/sessions\"\n",
    "  response = rest_api_helper(uri, \"GET\")\n",
    "  return response\n",
    "\n",
    "\n",
    "# https://cloud.google.com/spanner/docs/reference/rest/v1/projects.instances.databases.sessions/create\n",
    "def create_spanner_session(project_id = project_id, spanner_instance = spanner_instance, spanner_database = spanner_database):\n",
    "  print(\"No Spanner session found. Creating a new session.\")\n",
    "\n",
    "  # Create a new session\n",
    "  uri = f\"https://spanner.googleapis.com/v1/projects/{project_id}/instances/{spanner_instance}/databases/{spanner_database}/sessions\"\n",
    "  params = {\n",
    "      \"database\": f\"projects/{project_id}/instances/{spanner_instance}/databases/{spanner_database}\"\n",
    "  }\n",
    "  response = rest_api_helper(uri, \"POST\", {}, params)\n",
    "  return response['name']\n",
    "     \n",
    "\n",
    "# https://cloud.google.com/spanner/docs/reference/rest/v1/projects.instances.databases.sessions/delete\n",
    "def close_spanner_session(session, project_id = project_id, spanner_instance = spanner_instance, spanner_database = spanner_database):\n",
    "  \"\"\"\n",
    "  Example:\n",
    "    response = get_spanner_sessions()\n",
    "    for session in response['sessions']:\n",
    "      close_spanner_session(session['name'])\n",
    "  \"\"\"\n",
    "\n",
    "  uri = f\"https://spanner.googleapis.com/v1/{session}\"\n",
    "  response = rest_api_helper(uri, \"DELETE\", {}, {\"name\": f\"{session}\"})\n",
    "  return response\n",
    "     \n",
    "\n",
    "def run_spanner_query(sql, spanner_database = spanner_database, query_options=None, create_new_session=False):\n",
    "  \"\"\"\n",
    "  Runs a Spanner query and returns the result.\n",
    "\n",
    "  Args:\n",
    "      sql: The SQL query to execute.\n",
    "      query_options: (Optional) A dictionary of advanced query options.\n",
    "                    See https://cloud.google.com/spanner/docs/reference/rest/v1/projects.instances.databases.sessions/executeSql#queryoptions\n",
    "                    for available options.\n",
    "\n",
    "  Returns:\n",
    "      A dictionary containing the query results.\n",
    "\n",
    "  Ref:\n",
    "      https://cloud.google.com/spanner/docs/reference/rest/v1/projects.instances.databases.sessions/executeSql\n",
    "  \"\"\"\n",
    "  # Ensure a session exists\n",
    "  # Create session\n",
    "  global session\n",
    "  if not session or create_new_session == True:\n",
    "    session = create_spanner_session()\n",
    "\n",
    "  # Initialize response vars\n",
    "  commit_response = \"\"\n",
    "  response = \"\"\n",
    "\n",
    "  # Construct the request URL\n",
    "  uri = f\"https://spanner.googleapis.com/v1/{session}:executeSql\"\n",
    "\n",
    "  # Set transaction type (readOnly/readWrite) and transaction object with commit type (begin/singleUse)\n",
    "  transaction_type = \"readWrite\" if any(x in sql.lower() for x in [\"insert\", \"update\", \"delete\"]) else \"readOnly\"\n",
    "  transaction = {\"begin\": {\"readWrite\": {}}} if transaction_type == \"readWrite\" else {\"singleUse\": {\"readOnly\": {}}}\n",
    "\n",
    "  request_body = {\n",
    "      \"sql\": sql,\n",
    "      \"transaction\": transaction\n",
    "  }\n",
    "  params = {\n",
    "      \"session\": session\n",
    "  }\n",
    "\n",
    "  if query_options:\n",
    "      request_body[\"queryOptions\"] = query_options\n",
    "\n",
    "  try:\n",
    "    # Make the request\n",
    "    response = rest_api_helper(uri, \"POST\", request_body=request_body, params = params)\n",
    "\n",
    "  except RuntimeError as e:\n",
    "    if \"Session not found\" in str(e):\n",
    "      print(f\"Session not found. Creating a new session and retrying the query...\")\n",
    "      return run_spanner_query(sql, spanner_database, query_options, create_new_session=True)  # Retry with a new session\n",
    "    else:\n",
    "      raise  # Re-raise the exception if it's not a \"Session not found\" error\n",
    "\n",
    "  # Commit transaction if read/write\n",
    "  if transaction_type == \"readWrite\":\n",
    "      uri = f\"https://spanner.googleapis.com/v1/{session}:commit\"\n",
    "      params = {\n",
    "          \"session\": session\n",
    "      }\n",
    "      commit_response = rest_api_helper(uri, \"POST\", {\"transactionId\": response['metadata']['transaction']['id']}, params)\n",
    "      print(f\"commit_response: {commit_response}\")\n",
    "\n",
    "  # Return a DataFrame if\n",
    "  if (sql.lower().strip().startswith((\"select\", \"with\", \"graph\"))):\n",
    "    columns = [field.get('name', 'unnamed_column') for field in response['metadata']['rowType']['fields']]\n",
    "\n",
    "    # Create DataFrame from rows\n",
    "    if 'rows' in response:\n",
    "      df = pd.DataFrame(response['rows'], columns=columns)\n",
    "      return df\n",
    "    else:\n",
    "      return response\n",
    "\n",
    "  else:\n",
    "    # Return the query results\n",
    "    return response\n",
    "\n",
    "\n",
    "def run_spanner_ddl(ddl_array, project_id = project_id, spanner_instance = spanner_instance, spanner_database = spanner_database):\n",
    "  # Create tables in Spanner\n",
    "  # https://cloud.google.com/spanner/docs/reference/rest/v1/projects.instances.databases.tables/create#try-it\n",
    "\n",
    "  uri = f\"https://spanner.googleapis.com/v1/projects/{project_id}/instances/{spanner_instance}/databases/{spanner_database}/ddl\"\n",
    "  http_verb = \"PATCH\"\n",
    "  request_body = {\n",
    "      \"statements\": ddl_array\n",
    "  }\n",
    "\n",
    "  response = rest_api_helper(uri, http_verb, request_body)\n",
    "\n",
    "  operation_name = response['name']\n",
    "  uri = f\"https://spanner.googleapis.com/v1/{operation_name}\"\n",
    "\n",
    "  while True:\n",
    "    response = rest_api_helper(uri, \"GET\", {})\n",
    "    if response.get(\"done\", False):\n",
    "      if response.get(\"error\"):\n",
    "        print(response.get(\"error\"))\n",
    "      else:\n",
    "        print(\"Operation completed successfully.\")\n",
    "      break\n",
    "    else:\n",
    "      print(\"Operation not completed yet.\")\n",
    "      time.sleep(2)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f47c9f67-ab0a-419f-bb2e-2ccb3b94be33",
   "metadata": {},
   "source": [
    "## Finalize AlloyDB Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3989a7a1-7fd3-4fa4-9782-a69029594a71",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Connect to the AlloyDB Cluster\n",
    "\n",
    "This function will create a connection pool to your AlloyDB instance using the AlloyDB Python connector. The AlloyDB Python connector will automatically create secure connections to your AlloyDB instance using mTLS."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20e8c550-32a1-4bab-b1ed-86b85ea92252",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import asyncpg\n",
    "\n",
    "import sqlalchemy\n",
    "from sqlalchemy.ext.asyncio import AsyncEngine, create_async_engine\n",
    "\n",
    "from google.cloud.alloydb.connector import AsyncConnector, IPTypes\n",
    "\n",
    "async def init_connection_pool(connector: AsyncConnector, db_name: str = alloydb_database, pool_size: int = 5) -> AsyncEngine:\n",
    "    # initialize Connector object for connections to AlloyDB\n",
    "    connection_string = f\"projects/{project_id}/locations/{region}/clusters/{alloydb_cluster}/instances/{alloydb_instance}\"\n",
    "\n",
    "    async def getconn() -> asyncpg.Connection:\n",
    "        conn: asyncpg.Connection = await connector.connect(\n",
    "            connection_string,\n",
    "            \"asyncpg\",\n",
    "            user=\"postgres\",\n",
    "            password=alloydb_password,\n",
    "            db=db_name,\n",
    "            ip_type=IPTypes.PRIVATE, # Optionally use IPTypes.PUBLIC\n",
    "        )\n",
    "        return conn\n",
    "\n",
    "    pool = create_async_engine(\n",
    "        \"postgresql+asyncpg://\",\n",
    "        async_creator=getconn,\n",
    "        pool_size=pool_size,\n",
    "        max_overflow=0,\n",
    "        isolation_level='AUTOCOMMIT'\n",
    "    )\n",
    "    return pool\n",
    "\n",
    "connector = AsyncConnector()\n",
    "\n",
    "postgres_db_pool = await init_connection_pool(connector, \"postgres\")\n",
    "finance_db_pool = await init_connection_pool(connector, f\"{alloydb_database}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4428954-f1cd-4531-8593-176622679975",
   "metadata": {},
   "source": [
    "### Create AlloyDB Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9710f6f0-e620-4a04-bf2e-a7d62251004a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use postgres_db_pool to create the database\n",
    "sql = f\"CREATE DATABASE {alloydb_database};\"\n",
    "result = await run_alloydb_query(postgres_db_pool, sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c32c2e09-083f-445b-8ae5-3eaeb19ead2c",
   "metadata": {},
   "source": [
    "### Import AlloyDB Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "979b46ab-243f-4693-a86d-0090c4c4cdeb",
   "metadata": {},
   "source": [
    "#### Run the Import\n",
    "\n",
    "This step generally takes about 5 minutes to complete."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cdb4e06f-4bc6-45da-84ae-9c58f7611c94",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reference: https://cloud.google.com/sdk/gcloud/reference/alloydb/clusters/import\n",
    "!gcloud alloydb clusters import {alloydb_cluster} --region={region} --database={alloydb_database} --gcs-uri=gs://pr-public-demo-data/adk-toolbox-demo/data/finance.sql --sql --user=postgres"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21a10748-0fbb-4ec9-9418-8520903a5b0d",
   "metadata": {},
   "source": [
    "#### Validate Row Counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a843d49-788b-4c61-b01e-3ae43bec8885",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "SELECT 'users' AS table_name, (SELECT COUNT(*) FROM users) AS imported_count, 2000 AS target_row_count\n",
    "UNION ALL\n",
    "SELECT 'mcc_codes', (SELECT COUNT(*) FROM mcc_codes), 109\n",
    "UNION ALL\n",
    "SELECT 'transactions', (SELECT COUNT(*) FROM transactions), 13305915\n",
    "UNION ALL\n",
    "SELECT 'fraud_labels', (SELECT COUNT(*) FROM fraud_labels), 8914963\n",
    "UNION ALL\n",
    "SELECT 'cards', (SELECT COUNT(*) FROM cards), 6146;\n",
    "\"\"\"\n",
    "\n",
    "await run_alloydb_query(finance_db_pool, sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99cf7c6e",
   "metadata": {},
   "source": [
    "## Configure Least Privilege"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a83ce2a5",
   "metadata": {},
   "source": [
    "### Create a Least Privilege AlloyDB User for MCP Toolbox\n",
    "\n",
    "MCP Toolbox will use the `toolbox_user` principal to securely access the AlloyDB database. This will be a read-only user to ensure so inadvertent data or object changes are made with our Agent. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "432cab97",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_array = []\n",
    "\n",
    "sql_array.append(f\"\"\"\n",
    "-- Create the read-only user\n",
    "CREATE USER toolbox_user WITH PASSWORD '{alloydb_toolbox_user_password}';\n",
    "\"\"\")\n",
    "\n",
    "sql_array.append(\"\"\"\n",
    "-- Grant the user permission to connect to the database\n",
    "GRANT CONNECT ON DATABASE finance TO toolbox_user;\n",
    "\"\"\")\n",
    "\n",
    "sql_array.append(\"\"\"\n",
    "-- Grant usage on the public schema\n",
    "GRANT USAGE ON SCHEMA public TO toolbox_user;\n",
    "\"\"\")\n",
    "\n",
    "sql_array.append(\"\"\"\n",
    "-- Grant select (read) access on all existing tables in the public schema\n",
    "GRANT SELECT ON ALL TABLES IN SCHEMA public TO toolbox_user;\n",
    "\"\"\")\n",
    "\n",
    "sql_array.append(\"\"\"\n",
    "-- Grant usage on all sequences in the public schema\n",
    "GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO toolbox_user;\n",
    "\"\"\")\n",
    "\n",
    "sql_array.append(\"\"\"\n",
    "-- Ensure the user gets read access to any new tables created in the future\n",
    "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO toolbox_user;\n",
    "\"\"\")\n",
    "\n",
    "for sql in sql_array:\n",
    "    await run_alloydb_query(finance_db_pool, sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5affae92",
   "metadata": {},
   "source": [
    "### Spanner Least Privilege for MCP Toolbox\n",
    "\n",
    "MCP Toolbox will use the credentials of the Cloud Run service account that hosts it to access Spanner. We created a dedicated service account called `toolbox-service-account` via Terraform with the `roles/databaseUser` role for this purpose. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "859a1b58-1fbd-475f-b353-1a69c5fceacb",
   "metadata": {},
   "source": [
    "## Update Dates\n",
    "\n",
    "Our data set is several years old. We'll update the dates to show off the real-time capabilities of leveraging MCP Toolbox with ADK."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7510517-dda6-4e63-8e4e-f99246d59bd0",
   "metadata": {},
   "source": [
    "### Update AlloyDB Dates"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "684a5134-6fce-4297-8e10-db39dd37c5e9",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Update Transaction Dates\n",
    "\n",
    "> NOTE: This update may take 15-20 minutes as there are over 13 Million rows to update in the transactions table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7707465a-2f2f-47b6-87b3-5cbf8f32d229",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# See current MAX and MIN dates\n",
    "sql = \"SELECT MAX(date) AS max_data, MIN(date) AS min_date FROM transactions;\"\"\"\n",
    "await run_alloydb_query(finance_db_pool, sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c389312-1561-4f43-823e-63df5953f1d2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# This query shifts all transaction dates forward. \n",
    "# The most recent transaction will be updated to the current time (NOW()),\n",
    "# and all other transactions will be adjusted proportionally,\n",
    "# preserving the original time intervals between them. This is done to simulate\n",
    "# real-time data for the purpose of this lab.\n",
    "sql = \"UPDATE transactions SET date = NOW() - ((SELECT MAX(date) FROM transactions) - date);\"\n",
    "await run_alloydb_query(finance_db_pool, sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2763dbc8-3f50-4a51-a6ae-de0c92523598",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# See new MAX and MIN dates\n",
    "sql = \"SELECT MAX(date) AS max_data, MIN(date) AS min_date FROM transactions;\"\"\"\n",
    "await run_alloydb_query(finance_db_pool, sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3edfcc92-e513-42e6-a6ea-54970124a38d",
   "metadata": {},
   "source": [
    "#### Update Card Dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e4768aa-6abe-4c61-897b-8c7f57624aa8",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# See current MAX and MIN dates\n",
    "sql = \"\"\"\n",
    "SELECT MAX(acct_open_date) AS max_acct_open_date, \n",
    "    MIN(acct_open_date) AS min_acct_open_date,\n",
    "    MAX(year_pin_last_changed) AS max_year_pin_last_changed, \n",
    "    MIN(year_pin_last_changed) AS min_year_pin_last_changed\n",
    "FROM cards;\"\"\"\n",
    "await run_alloydb_query(finance_db_pool, sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c121ece-085d-462c-8a7d-acd017631812",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# This query shifts all transaction dates forward. \n",
    "# The most recent record will be updated to the current time (NOW()),\n",
    "# and all other record will be adjusted proportionally,\n",
    "# preserving the original time intervals between them.\n",
    "sql = \"\"\"\n",
    "UPDATE cards\n",
    "SET\n",
    "    acct_open_date = (NOW() - ((SELECT MAX(acct_open_date) FROM cards) - acct_open_date) * INTERVAL '1 day')::date,\n",
    "    year_pin_last_changed = year_pin_last_changed + (EXTRACT(YEAR FROM NOW())::INTEGER - (SELECT MAX(year_pin_last_changed) FROM cards));\n",
    "\"\"\"\n",
    "await run_alloydb_query(finance_db_pool, sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ec0749f-a36e-4cef-9771-c0f40406c4d7",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# See new MAX and MIN dates\n",
    "sql = \"\"\"\n",
    "SELECT MAX(acct_open_date) AS max_acct_open_date, \n",
    "    MIN(acct_open_date) AS min_acct_open_date,\n",
    "    MAX(year_pin_last_changed) AS max_year_pin_last_changed, \n",
    "    MIN(year_pin_last_changed) AS min_year_pin_last_changed\n",
    "FROM cards;\"\"\"\n",
    "await run_alloydb_query(finance_db_pool, sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7c3ada3-1a50-4332-a3cf-49c724eebaf3",
   "metadata": {},
   "source": [
    "### Update Spanner Dates"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56ae9ced",
   "metadata": {},
   "source": [
    "#### Verify Spanner Row Counts\n",
    "\n",
    "> IMPORTANT: If data is missing from Spanner, check the status of the `import-spanner` job in [Dataflow](https://console.cloud.google.com/dataflow/jobs). Resolve any errors and re-run the job before proceeding. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29e699d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "SELECT 'Account' AS table_name, (SELECT COUNT(*) FROM Account) AS row_count, 700 AS target_row_count\n",
    "UNION ALL\n",
    "SELECT 'Loan', (SELECT COUNT(*) FROM Loan), 500\n",
    "UNION ALL\n",
    "SELECT 'Person', (SELECT COUNT(*) FROM Person), 500\n",
    "UNION ALL\n",
    "SELECT 'AccountTransferAccount', (SELECT COUNT(*) FROM AccountTransferAccount), 500\n",
    "UNION ALL\n",
    "SELECT 'AccountRepayLoan', (SELECT COUNT(*) FROM AccountRepayLoan), 500\n",
    "UNION ALL\n",
    "SELECT 'PersonOwnAccount', (SELECT COUNT(*) FROM PersonOwnAccount), 700\n",
    "UNION ALL\n",
    "SELECT 'AccountAudits', (SELECT COUNT(*) FROM AccountAudits), 500;\n",
    "\"\"\"\n",
    "\n",
    "result = run_spanner_query(sql)\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0e9e280-7a3b-4e5f-b949-0ad7e721062a",
   "metadata": {},
   "source": [
    "#### View Current Min and Max Dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b1a4db0-9d0c-4f0b-8f7d-7d170a7e5dee",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "SELECT 'Account' AS table_name, MAX(create_time) AS max_time, MIN(create_time) AS min_time FROM Account\n",
    "UNION ALL\n",
    "SELECT 'AccountAudits', MAX(audit_timestamp), MIN(audit_timestamp) FROM AccountAudits\n",
    "UNION ALL\n",
    "SELECT 'AccountRepayLoan', MAX(create_time), MIN(create_time) FROM AccountRepayLoan\n",
    "UNION ALL\n",
    "SELECT 'AccountTransferAccount', MAX(create_time), MIN(create_time) FROM AccountTransferAccount\n",
    "UNION ALL\n",
    "SELECT 'Loan', MAX(create_time), MIN(create_time) FROM Loan\n",
    "UNION ALL\n",
    "SELECT 'PersonOwnAccount', MAX(create_time), MIN(create_time) FROM PersonOwnAccount;\n",
    "\"\"\"\n",
    "\n",
    "run_spanner_query(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "117d4473-c652-4a49-abc2-d7f86da86d46",
   "metadata": {},
   "source": [
    "#### Update Date Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43517177-43ce-42c1-9013-3c19c8495000",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql_array = []\n",
    "\n",
    "sql_array.append(\"\"\"\n",
    "UPDATE Account\n",
    "SET create_time = TIMESTAMP_SUB(\n",
    "  CURRENT_TIMESTAMP(),\n",
    "  INTERVAL TIMESTAMP_DIFF(\n",
    "    (SELECT MAX(t.create_time) FROM Account AS t),\n",
    "    create_time,\n",
    "    MICROSECOND\n",
    "  ) MICROSECOND\n",
    ")\n",
    "WHERE create_time IS NOT NULL;\n",
    "\"\"\")\n",
    "\n",
    "sql_array.append(\"\"\"\n",
    "UPDATE Loan\n",
    "SET create_time = TIMESTAMP_SUB(\n",
    "  CURRENT_TIMESTAMP(),\n",
    "  INTERVAL TIMESTAMP_DIFF(\n",
    "    (SELECT MAX(t.create_time) FROM Loan AS t),\n",
    "    create_time,\n",
    "    MICROSECOND\n",
    "  ) MICROSECOND\n",
    ")\n",
    "WHERE create_time IS NOT NULL;\n",
    "\"\"\")\n",
    "\n",
    "sql_array.append(\"\"\"\n",
    "UPDATE PersonOwnAccount\n",
    "SET create_time = TIMESTAMP_SUB(\n",
    "  CURRENT_TIMESTAMP(),\n",
    "  INTERVAL TIMESTAMP_DIFF(\n",
    "    (SELECT MAX(t.create_time) FROM PersonOwnAccount AS t),\n",
    "    create_time,\n",
    "    MICROSECOND\n",
    "  ) MICROSECOND\n",
    ")\n",
    "WHERE create_time IS NOT NULL;\n",
    "\"\"\")\n",
    "\n",
    "for sql in sql_array:\n",
    "    result = run_spanner_query(sql)\n",
    "    print(f\"{result}\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8087fcb7-fe96-4f38-81a6-76135889fdfa",
   "metadata": {},
   "source": [
    "#### Handle Primary Key Column Updates\n",
    "\n",
    "This code handles a special case where timestampe columns are part of the Primary Key on three of our tables. Spanner does not allow updates on key columns. To work around this, we will:\n",
    "1. Create temporary tables with the same schema.\n",
    "2. Copy the data from the original tables to the temporary tables, updating the timestamps in the process.\n",
    "3. Delete the data from the original tables.\n",
    "4. Reload the data from the temporary tables back into the original tables.\n",
    "5. Drop the temporary tables."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a7efc07-0e87-45d7-8051-8a2e49e6d68c",
   "metadata": {},
   "source": [
    "#### Create Temp Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f70d0fa3-4609-43c6-b3fc-f8b53b6f77a1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "ddl_array = []\n",
    "\n",
    "ddl_array.append(\"\"\"\n",
    "CREATE TABLE AccountAuditsTemp (\n",
    "  id INT64 NOT NULL,\n",
    "  audit_timestamp TIMESTAMP,\n",
    "  audit_details STRING(MAX),\n",
    ") PRIMARY KEY(id, audit_timestamp)\n",
    "\"\"\")\n",
    "\n",
    "ddl_array.append(\"\"\"\n",
    "CREATE TABLE AccountRepayLoanTemp (\n",
    "  id INT64 NOT NULL,\n",
    "  loan_id INT64 NOT NULL,\n",
    "  amount FLOAT64,\n",
    "  create_time TIMESTAMP NOT NULL,\n",
    ") PRIMARY KEY(id, loan_id, create_time)\n",
    "\"\"\")\n",
    "\n",
    "ddl_array.append(\"\"\"\n",
    "CREATE TABLE AccountTransferAccountTemp (\n",
    "  id INT64 NOT NULL,\n",
    "  to_id INT64 NOT NULL,\n",
    "  amount FLOAT64,\n",
    "  create_time TIMESTAMP NOT NULL,\n",
    ") PRIMARY KEY(id, to_id, create_time)\n",
    "\"\"\")\n",
    "\n",
    "run_spanner_ddl(ddl_array)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1cdaf72b-276c-4d87-9258-21929bda0b34",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Insert data into temp tables with new timestamps\n",
    "sql_array = [] \n",
    "\n",
    "sql_array.append(\n",
    "\"\"\"\n",
    "INSERT INTO AccountAuditsTemp (id, audit_timestamp, audit_details)\n",
    "SELECT\n",
    "    id,\n",
    "    TIMESTAMP_SUB(\n",
    "        CURRENT_TIMESTAMP(),\n",
    "        INTERVAL TIMESTAMP_DIFF(\n",
    "            (SELECT MAX(t.audit_timestamp) FROM AccountAudits AS t),\n",
    "            audit_timestamp,\n",
    "            MICROSECOND\n",
    "        ) MICROSECOND\n",
    "    ) AS audit_timestamp,\n",
    "    audit_details\n",
    "FROM\n",
    "    AccountAudits\n",
    "\"\"\"\n",
    ")\n",
    "\n",
    "sql_array.append(\"\"\"\n",
    "INSERT INTO AccountRepayLoanTemp (id, loan_id, amount, create_time)\n",
    "SELECT\n",
    "    id,\n",
    "    loan_id,\n",
    "    amount,\n",
    "    TIMESTAMP_SUB(\n",
    "        CURRENT_TIMESTAMP(),\n",
    "        INTERVAL TIMESTAMP_DIFF(\n",
    "            (SELECT MAX(t.create_time) FROM AccountRepayLoan AS t),\n",
    "            create_time,\n",
    "            MICROSECOND\n",
    "        ) MICROSECOND\n",
    "    ) AS create_time\n",
    "FROM\n",
    "    AccountRepayLoan\n",
    "\"\"\")\n",
    "\n",
    "sql_array.append(\"\"\"\n",
    "INSERT INTO AccountTransferAccountTemp (id, to_id, amount, create_time)\n",
    "SELECT\n",
    "    id,\n",
    "    to_id,\n",
    "    amount,\n",
    "    TIMESTAMP_SUB(\n",
    "        CURRENT_TIMESTAMP(),\n",
    "        INTERVAL TIMESTAMP_DIFF(\n",
    "            (SELECT MAX(t.create_time) FROM AccountTransferAccount AS t),\n",
    "            create_time,\n",
    "            MICROSECOND\n",
    "        ) MICROSECOND\n",
    "    ) AS create_time\n",
    "FROM\n",
    "    AccountTransferAccount\n",
    "\"\"\")\n",
    "\n",
    "for sql in sql_array:\n",
    "    run_spanner_query(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f21b3a1-a032-45a3-ba27-e3ff113e94de",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# View timestamp data in temp tables\n",
    "\n",
    "sql = \"\"\"\n",
    "SELECT 'AccountAuditsTemp' AS table_name, MAX(audit_timestamp) AS max_time, MIN(audit_timestamp) AS min_time FROM AccountAuditsTemp\n",
    "UNION ALL\n",
    "SELECT 'AccountRepayLoanTemp', MAX(create_time), MIN(create_time) FROM AccountRepayLoanTemp\n",
    "UNION ALL\n",
    "SELECT 'AccountTransferAccountTemp', MAX(create_time), MIN(create_time) FROM AccountTransferAccountTemp\n",
    "\"\"\"\n",
    "run_spanner_query(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "113fdc2e-41e1-408e-94d2-36eb06542cdb",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Reload Data into Source Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cd3706f4-1a3c-4a9f-a7dc-a7a3998a7a54",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Delete data in source tables\n",
    "sql_array = []\n",
    "\n",
    "sql_array.append(\"DELETE FROM AccountAudits WHERE 1 = 1\")\n",
    "\n",
    "sql_array.append(\"DELETE FROM AccountRepayLoan WHERE 1 = 1\")\n",
    "\n",
    "sql_array.append(\"DELETE FROM AccountTransferAccount WHERE 1 = 1\")\n",
    "\n",
    "for sql in sql_array:\n",
    "    run_spanner_query(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b310808e-3e26-4ec4-8a4c-5985812714d1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Load source tables from temp tables\n",
    "\n",
    "sql_array = []\n",
    "\n",
    "sql_array.append(\n",
    "\"\"\"\n",
    "INSERT INTO AccountAudits (id, audit_timestamp, audit_details)\n",
    "SELECT\n",
    "    id,\n",
    "    audit_timestamp,\n",
    "    audit_details\n",
    "FROM\n",
    "    AccountAuditsTemp\n",
    "\"\"\"\n",
    ")\n",
    "\n",
    "sql_array.append(\"\"\"\n",
    "INSERT INTO AccountRepayLoan (id, loan_id, amount, create_time)\n",
    "SELECT\n",
    "    id,\n",
    "    loan_id,\n",
    "    amount,\n",
    "    create_time\n",
    "FROM\n",
    "    AccountRepayLoanTemp\n",
    "\"\"\")\n",
    "\n",
    "sql_array.append(\"\"\"\n",
    "INSERT INTO AccountTransferAccount (id, to_id, amount, create_time)\n",
    "SELECT\n",
    "    id,\n",
    "    to_id,\n",
    "    amount,\n",
    "    create_time\n",
    "FROM\n",
    "    AccountTransferAccountTemp\n",
    "\"\"\")\n",
    "\n",
    "for sql in sql_array:\n",
    "    run_spanner_query(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06338ffb-538a-475c-9c2e-318dcba2a1eb",
   "metadata": {},
   "source": [
    "#### View New Min and Max Dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ccf1249-4ed8-44d4-baa6-67346a4c1a45",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "SELECT 'Account' AS table_name, MAX(create_time) AS max_time, MIN(create_time) AS min_time FROM Account\n",
    "UNION ALL\n",
    "SELECT 'AccountAudits', MAX(audit_timestamp), MIN(audit_timestamp) FROM AccountAudits\n",
    "UNION ALL\n",
    "SELECT 'AccountRepayLoan', MAX(create_time), MIN(create_time) FROM AccountRepayLoan\n",
    "UNION ALL\n",
    "SELECT 'AccountTransferAccount', MAX(create_time), MIN(create_time) FROM AccountTransferAccount\n",
    "UNION ALL\n",
    "SELECT 'Loan', MAX(create_time), MIN(create_time) FROM Loan\n",
    "UNION ALL\n",
    "SELECT 'PersonOwnAccount', MAX(create_time), MIN(create_time) FROM PersonOwnAccount;\n",
    "\"\"\"\n",
    "\n",
    "run_spanner_query(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc979003-a664-486a-ab0f-b25d8c9d36a3",
   "metadata": {},
   "source": [
    "#### Clean Up Temp Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6db6a2f-46b3-4073-bbc7-38c1af67e29f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "ddl_array = []\n",
    "\n",
    "ddl_array.append(\"\"\"\n",
    "DROP TABLE AccountAuditsTemp\n",
    "\"\"\")\n",
    "\n",
    "ddl_array.append(\"\"\"\n",
    "DROP TABLE AccountRepayLoanTemp\n",
    "\"\"\")\n",
    "\n",
    "ddl_array.append(\"\"\"\n",
    "DROP TABLE AccountTransferAccountTemp\n",
    "\"\"\")\n",
    "\n",
    "run_spanner_ddl(ddl_array)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cc7a91d-99d8-4ab7-b6c1-c64fb54a20aa",
   "metadata": {},
   "source": [
    "#### Verify Spanner Row Counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a5405dc-de7e-4834-9e30-ce7aba8624ed",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "SELECT 'Account' AS table_name, (SELECT COUNT(*) FROM Account) AS row_count, 700 AS target_row_count\n",
    "UNION ALL\n",
    "SELECT 'Loan', (SELECT COUNT(*) FROM Loan), 500\n",
    "UNION ALL\n",
    "SELECT 'Person', (SELECT COUNT(*) FROM Person), 500\n",
    "UNION ALL\n",
    "SELECT 'AccountTransferAccount', (SELECT COUNT(*) FROM AccountTransferAccount), 500\n",
    "UNION ALL\n",
    "SELECT 'AccountRepayLoan', (SELECT COUNT(*) FROM AccountRepayLoan), 500\n",
    "UNION ALL\n",
    "SELECT 'PersonOwnAccount', (SELECT COUNT(*) FROM PersonOwnAccount), 700\n",
    "UNION ALL\n",
    "SELECT 'AccountAudits', (SELECT COUNT(*) FROM AccountAudits), 500;\n",
    "\"\"\"\n",
    "\n",
    "result = run_spanner_query(sql)\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63acda37-ec30-4925-be23-79eedcbf72d1",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Explore AlloyDB Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "418045c3-23a6-4d73-b667-ca01e1490050",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### Review AlloyDB Schema\n",
    "\n",
    "The AlloyDB database contains data related to transactions, credit cards, users, mcc codes, and historical fraud cases. See the ERD diagram below for details on the tables, columns, and relationships. \n",
    "\n",
    "![AlloyDB Schema](img/alloydb_finance_db_erd.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b94f89e-7aab-473b-8e47-03ea6bcba782",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Run AlloyDB Queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66b32fb8-d08b-43a4-9d99-ea71c77fc36f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM transactions LIMIT 10;\"\n",
    "await run_alloydb_query(finance_db_pool, sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0a846831-041f-46cf-a4ae-ff1ac0f38026",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM cards LIMIT 10;\"\n",
    "await run_alloydb_query(finance_db_pool, sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7451704e-91fa-4e2a-bfbc-b47d720c8866",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM users LIMIT 10;\"\n",
    "await run_alloydb_query(finance_db_pool, sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "384f234c-bdc2-4011-9700-a8218044d0dc",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM mcc_codes LIMIT 10;\"\n",
    "await run_alloydb_query(finance_db_pool, sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78c19f6c-a4b4-432b-8d7f-6a656cab1f26",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM fraud_labels LIMIT 10;\"\n",
    "await run_alloydb_query(finance_db_pool, sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f34b452-4d25-4792-858a-068779ffe568",
   "metadata": {},
   "source": [
    "## Explore Spanner Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c18a007-09df-4f92-b1ca-befaeb5aa733",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Review the Spanner Schema"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb3ba9e7-7263-48bc-a93c-29f1935f3dad",
   "metadata": {
    "tags": []
   },
   "source": [
    "The Spanner dataset is already loaded for you via Terraform automation. It contains data about accounts, account transfers, account audits, loans, and loan repayments.\n",
    "\n",
    "The schema is defined as follows:\n",
    "\n",
    "```\n",
    "CREATE TABLE Account (\n",
    "  id INT64 NOT NULL,\n",
    "  create_time TIMESTAMP,\n",
    "  is_blocked BOOL,\n",
    "  type STRING(MAX),\n",
    ") PRIMARY KEY(id);\n",
    "\n",
    "CREATE TABLE AccountAudits (\n",
    "  id INT64 NOT NULL,\n",
    "  audit_timestamp TIMESTAMP,\n",
    "  audit_details STRING(MAX),\n",
    ") PRIMARY KEY(id, audit_timestamp),\n",
    "  INTERLEAVE IN PARENT Account ON DELETE CASCADE;\n",
    "\n",
    "CREATE TABLE AccountRepayLoan (\n",
    "  id INT64 NOT NULL,\n",
    "  loan_id INT64 NOT NULL,\n",
    "  amount FLOAT64,\n",
    "  create_time TIMESTAMP NOT NULL,\n",
    ") PRIMARY KEY(id, loan_id, create_time),\n",
    "  INTERLEAVE IN PARENT Account ON DELETE CASCADE;\n",
    "\n",
    "CREATE TABLE AccountTransferAccount (\n",
    "  id INT64 NOT NULL,\n",
    "  to_id INT64 NOT NULL,\n",
    "  amount FLOAT64,\n",
    "  create_time TIMESTAMP NOT NULL,\n",
    ") PRIMARY KEY(id, to_id, create_time),\n",
    "  INTERLEAVE IN PARENT Account ON DELETE CASCADE;\n",
    "\n",
    "CREATE TABLE Loan (\n",
    "  id INT64 NOT NULL,\n",
    "  loan_amount FLOAT64,\n",
    "  balance FLOAT64,\n",
    "  create_time TIMESTAMP,\n",
    "  interest_rate FLOAT64,\n",
    ") PRIMARY KEY(id);\n",
    "\n",
    "CREATE TABLE Person (\n",
    "  id INT64 NOT NULL,\n",
    "  name STRING(MAX),\n",
    ") PRIMARY KEY(id);\n",
    "\n",
    "CREATE TABLE PersonOwnAccount (\n",
    "  id INT64 NOT NULL,\n",
    "  account_id INT64 NOT NULL,\n",
    "  create_time TIMESTAMP,\n",
    ") PRIMARY KEY(id, account_id),\n",
    "  INTERLEAVE IN PARENT Person ON DELETE CASCADE;\n",
    "\n",
    "CREATE PROPERTY GRAPH FinGraph\n",
    "  NODE TABLES(\n",
    "    Account\n",
    "      KEY(id)\n",
    "      LABEL Account PROPERTIES(\n",
    "        create_time,\n",
    "        id,\n",
    "        is_blocked,\n",
    "        type),\n",
    "\n",
    "    Loan\n",
    "      KEY(id)\n",
    "      LABEL Loan PROPERTIES(\n",
    "        balance,\n",
    "        create_time,\n",
    "        id,\n",
    "        interest_rate,\n",
    "        loan_amount),\n",
    "\n",
    "    Person\n",
    "      KEY(id)\n",
    "      LABEL Person PROPERTIES(\n",
    "        id,\n",
    "        name)\n",
    "  )\n",
    "  EDGE TABLES(\n",
    "    AccountRepayLoan\n",
    "      KEY(id, loan_id, create_time)\n",
    "      SOURCE KEY(id) REFERENCES Account(id)\n",
    "      DESTINATION KEY(loan_id) REFERENCES Loan(id)\n",
    "      LABEL Repays PROPERTIES(\n",
    "        amount,\n",
    "        create_time,\n",
    "        id,\n",
    "        loan_id),\n",
    "\n",
    "    AccountTransferAccount\n",
    "      KEY(id, to_id, create_time)\n",
    "      SOURCE KEY(id) REFERENCES Account(id)\n",
    "      DESTINATION KEY(to_id) REFERENCES Account(id)\n",
    "      LABEL Transfers PROPERTIES(\n",
    "        amount,\n",
    "        create_time,\n",
    "        id,\n",
    "        to_id),\n",
    "\n",
    "    PersonOwnAccount\n",
    "      KEY(id, account_id)\n",
    "      SOURCE KEY(id) REFERENCES Person(id)\n",
    "      DESTINATION KEY(account_id) REFERENCES Account(id)\n",
    "      LABEL Owns PROPERTIES(\n",
    "        account_id,\n",
    "        create_time,\n",
    "        id)\n",
    "  );\n",
    "```\n",
    "\n",
    "Notice that the Spanner schema takes advantage of both the core relational database model, as well as Spanner's built-in property graph model. The high-level graph nodes and edges are visualized below. \n",
    "\n",
    "![Spanner Graph Schema](img/spanner_graph_schema.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26f5fbab-2f0c-4ca3-8615-32941a57cf25",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Run Relational Spanner Queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7704c93-655b-40fa-b9f0-62c254b2483c",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM Account LIMIT 10;\"\n",
    "run_spanner_query(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2f57f24-2bce-4907-9149-ecc47313a3e4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM AccountAudits LIMIT 10;\"\n",
    "run_spanner_query(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e755dd3b-965c-4575-9f3b-dc1ac175f03c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM AccountRepayLoan LIMIT 10;\"\n",
    "run_spanner_query(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4778c531-32ad-460f-b132-2e951c8181e0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM AccountTransferAccount LIMIT 10;\"\n",
    "run_spanner_query(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58e7ddf5-4062-4f49-841b-3299cb872a80",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM Loan LIMIT 10;\"\n",
    "run_spanner_query(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7910923c-1580-4482-b2a7-65604a09fbf3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM Person LIMIT 10;\"\n",
    "run_spanner_query(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62df9b42-09a2-455e-a462-fa55f91bfbab",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM Account LIMIT 10;\"\n",
    "run_spanner_query(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "926a25aa-3185-4ab6-b822-963eb3cd75f7",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Run Spanner Graph Queries\n",
    "\n",
    "Read more about running graph queries on Spanner in the [Spanner docs](https://cloud.google.com/spanner/docs/graph/queries-overview)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "236a3138-c7cb-41cb-b2d1-465f1e2aecc6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "GRAPH FinGraph\n",
    "MATCH\n",
    "  (person:Person {name: \"Jacoby\"})-[own:Owns]->\n",
    "  (account:Account)-[repay:Repays]->(loan:Loan)\n",
    "RETURN\n",
    "  account.id AS account_id,\n",
    "  repay.create_time AS repay_time,\n",
    "  repay.amount AS loan_repay_amount,\n",
    "  loan.id AS loan_id,\n",
    "  loan.loan_amount AS loan_amount\n",
    "ORDER BY repay.create_time;\n",
    "\"\"\"\n",
    "run_spanner_query(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c28e4129-df8e-4c69-a4d8-3cb694ffeacd",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "GRAPH FinGraph\n",
    "MATCH ANY SHORTEST\n",
    "  (src_accnt:Account {id:75} )-[transfers:Transfers]->{3,6}\n",
    "  (dst_accnt:Account {id:199})\n",
    "RETURN\n",
    "  ARRAY_LENGTH(transfers) AS num_hops,\n",
    "  TO_JSON(transfers) AS transfer_edges;\n",
    "\"\"\"\n",
    "\n",
    "run_spanner_query(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8d88059",
   "metadata": {},
   "source": [
    "Congratulations, you have completed Module 1! Proceed to [`2_deploy_mcp_toolbox.ipynb`](./2_deploy_mcp_toolbox.ipynb) to configure and deploy MCP Toolbox."
   ]
  }
 ],
 "metadata": {
  "environment": {
   "kernel": "conda-base-py",
   "name": "workbench-notebooks.m130",
   "type": "gcloud",
   "uri": "us-docker.pkg.dev/deeplearning-platform-release/gcr.io/workbench-notebooks:m130"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel) (Local)",
   "language": "python",
   "name": "conda-base-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
